import json
import time
import random

import requests
from win32com import client  # pip install pypiwin32
from retrying import retry

from common import logger

# from encapsulated.DataBase.mysqlconnector import MysqlDb

"""
不修改原有格式的情况下写入excel
"""


def excel_rw(read_excel_path, save_excel_path):
    """

    :param read_excel_path: 读取excel文件的路径
    :param wcol: 要读取/修改的列数
    :param save_excel_path: 保存excel文件的路径
    :return:
    """
    xlApp = client.Dispatch("Excel.Application")
    xlApp.Visible = False  # 软件不可见 True为可见
    xlApp.DisplayAlerts = False  # 不警告
    xlBook = xlApp.Workbooks.Open(read_excel_path)
    xlSheet = xlBook.Worksheets(1)  # 打开第1个表
    # xlSheet = xlBook.Worksheets("CSDN")  # 打开名为“CSDN”的sheet
    info = xlSheet.UsedRange
    nrows = info.Rows.Count  # sheet1行数
    ncols = info.Columns.Count  # sheet1列数
    # 操作逻辑
    # business1(xlSheet, nrows, ncols)
    business2(xlSheet, nrows, ncols)

    """
    修改格式
    """
    # 字体加粗
    # xlSheet.Cells(i, j).Font.Bold = True
    # 单元格背景颜色
    # xlSheet.Cells(i, j).Interior.ColorIndex = 45
    # 保存
    # xlBook.Save()
    # 另存为
    xlBook.SaveAs(save_excel_path)  # path 为另存路径
    # 退出表格
    xlBook.Close(True)
    # 关闭Excel软件，可关闭你打开的全部Excel文件
    xlApp.Quit()


@retry(stop_max_attempt_number=5, wait_fixed=5000)  # 重试次数、间隔时间ms
def test_api_qccSearch(key):
    headers = {'User-Agent': 'python-requests/2.27.1', 'Accept-Encoding': 'gzip, deflate', 'Accept': '*/*',
               'Connection': 'keep-alive',
               'authentication': 'USERID MTM2ODg4NDc3NTc6QTcwNnA1ZHlqODJUNEx6RjVNUXU5aUg4Ym5aS2ZxQzY6MQ==',
               'Content-Type': 'application/json'}
    data = {'keyword': key, 'limit': 10}
    r = requests.get("http://test_api.oiltour.cn/api/qccSearch", params=data, headers=headers)
    status_code = r.status_code
    ru = r.json()['data']
    logger.info(ru)
    if not ru:
        logger.info(key)
        raise ValueError
    return ru


def business2(xlSheet, nrows, ncols):
    company_name_dict = {}
    for i in range(2, nrows + 1):
        # 读取4 列数据
        city = xlSheet.Cells(i, 9).Value
        if city not in list(company_name_dict.keys()):
            company_name_dict[city] = []
        else:
            pass
        data = xlSheet.Cells(i, 4).Value
        company_name_dict[city].append(data)
    print(company_name_dict)
    return company_name_dict


def business1(xlSheet, nrows, ncols):
    for i in range(3, nrows + 1):
        data = xlSheet.Cells(i, 2).Value
        key = xlSheet.Cells(i, 3).Value
        if not key:
            key_list = test_api_qccSearch(data)
            for j in key_list:
                if j['Name'] == data:
                    # 写入
                    logger.info(j['CreditCode'])
                    xlSheet.Cells(i, 3).Value = "{}".format(j['CreditCode'])


def business(xlSheet, nrows, ncols):
    with MysqlDb() as db:
        print(db.query("SELECT * FROM `mall`.`tb_oiltour_mall_user` WHERE `login_name` = '15918709443' LIMIT 0,1000"))
        for i in range(1, nrows + 1):
            # 读取12 列数据
            data = xlSheet.Cells(i, 12).Value
            json_data = json.loads(data)
            # print(json_data)
            try:
                # print(json_data['link_phone'])
                # print(json_data['name'])
                # print(json_data['city'])
                # # 写入
                xlSheet.Cells(i, 13).Value = "{}".format(json_data['link_phone'])
                rt = db.query(
                    "SELECT * FROM `mall`.`tb_oiltour_mall_user` WHERE `login_name` = '{}' AND `nick_name` = '{}' LIMIT 0,1000".format(
                        json_data['link_phone'], json_data['name']))
                if len(rt) > 1:
                    print(rt)
                elif rt:
                    print(rt[0]['city'])

                city_dict = {"258": "武汉市", "277": "湘潭市", "289": "广州市", "291": "深圳市", "294": "佛山市", "295": "江门市",
                             "297": "茂名市", "298": "肇庆市",
                             "303": "阳江市", "305": "东莞市", "306": "中山市"}
                xlSheet.Cells(i, 14).Value = "{}".format(city_dict[str(json_data['city'])])
                xlSheet.Cells(i, 15).Value = "{}".format(1)
                xlSheet.Cells(i, 16).Value = "{}".format(rt[0]['city'])
            except:
                # print(json_data)
                pass


if __name__ == '__main__':
    excel_rw("C:\\Users\\Administrator\\Desktop\\用户列表2022 - 06 - 29.xlsx",
             "C:\\Users\\Administrator\\Desktop\\456.xlsx")
